package com.b2c.repository.pdd;

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.b2c.entity.pdd.FinanceMonthReportPddVo;
import com.b2c.entity.pdd.SalesAndRefundReportPddVo;
import com.b2c.entity.pdd.SalesReportPddVo;

@Repository
public class PddSalesReportRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public List<SalesReportPddVo> getSalesReport(Integer shopId,String startDate,String endDate){
        if(StringUtils.isEmpty(startDate)) return null;
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT A.*,eg.`name` as goodsName,eg.number as goodsNum,eg.id AS erpGoodsId,IFNULL(eg.freight,0) AS freight,");
        sql.append("egs.specNumber,IFNULL(egs.purPrice,0) cost_price,egs.color_image AS goodsImg,CONCAT(egs.color_value,egs.size_value,egs.style_value) AS goodsSpec" );
        sql.append(" FROM  ");
        sql.append(" (SELECT  oi.pdd_sku_id,");
        sql.append("SUM(itemAmount) AS amount,SUM(oi.quantity) quantity");
        sql.append(" FROM dc_pdd_orders_item oi ");
        sql.append(" left join dc_pdd_orders o ON o.id = oi.order_id ");
        sql.append(" WHERE oi.pdd_good_id <>419986003167 ");
        if(shopId!=null && shopId.intValue()>0){
            sql.append(" AND o.shopId="+shopId); 
        }
        sql.append("  AND (o.order_status=1 OR o.order_status=2 OR o.order_status=3) ");
        sql.append(" AND o.refund_status=1 ");
        
        
        List<Object> params = new ArrayList<>();
        

        if(StringUtils.hasText(endDate)){
            sql.append(" AND date_format(o.confirm_time,'%Y-%m-%d') >= ? ");
            params.add(startDate);
            sql.append(" AND date_format(o.confirm_time,'%Y-%m-%d') <= ?  ");
            params.add(endDate);
        }else{
            sql.append(" AND date_format(o.confirm_time,'%Y-%m-%d') = ? ");
            params.add(startDate);
        }


        sql.append(" GROUP BY oi.pdd_sku_id ) AS A ");

         sql.append(" LEFT JOIN dc_shop_goods_sku sgs ON sgs.skuId = A.pdd_sku_id ");
        sql.append(" LEFT JOIN erp_goods_spec egs ON egs.id = sgs.erp_goods_spec_id   ");
        sql.append(" LEFT JOIN erp_goods eg ON eg.id = sgs.erp_goods_id   ");
//        sql.append(" ORDER BY eg.number ");
        var lists = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper(SalesReportPddVo.class), params.toArray(new Object[params.size()]));
        return lists;
    }

    public List<SalesReportPddVo> getSendReport(String date,Integer shopId){
//        StringBuilder sql = new StringBuilder();
//        sql.append("SELECT eg.`name` as goodsName,eg.number as goodsNum,oi.goodsPrice,pdd_good_id,SUM(oi.itemAmount) AS amount,SUM(quantity) quantity,pd.thumbUrl as goodsImg,eg.id AS erpGoodsId,IFNULL(eg.cost_price,0) cost_price");
//        sql.append(" FROM dc_pdd_orders_item oi ");
//        sql.append(" left join dc_pdd_orders o ON o.id = oi.order_id ");
//        sql.append(" LEFT JOIN dc_shop_goods pd ON pd.goodsId = oi.pdd_good_id ");
//        sql.append(" LEFT JOIN erp_goods eg ON eg.id = pd.erp_goods_id ");
//        sql.append(" WHERE 1=1 ");
//        if(shopId!=null && shopId.intValue()>0){
//            sql.append(" AND o.shopId="+shopId);
//        }
//        sql.append("  AND ( o.order_status=2 OR o.order_status=3) ");
//
//        sql.append(" AND date_format(o.shipping_time,'%Y-%m-%d') = ? ");
//        sql.append(" GROUP BY pdd_good_id ");
//        var lists = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper(SalesReportPddVo.class), date);
//        return lists;
        StringBuilder ASQL = new StringBuilder("SELECT pdd_good_id, SUM(oi.itemAmount) AS amount, SUM(oi.quantity) quantity");
        ASQL.append(" FROM dc_pdd_orders_item oi ");
        ASQL.append(" LEFT JOIN dc_pdd_orders o ON o.id = oi.order_id ");
        ASQL.append(" WHERE 1=1  ");
        if(shopId!=null && shopId.intValue()>0){
            ASQL.append(" AND o.shopId="+shopId);
        }
        ASQL.append("  AND ( o.order_status=2 OR o.order_status=3) ");
        ASQL.append(" AND date_format(o.shipping_time,'%Y-%m-%d') = ? ");
        ASQL.append(" GROUP BY pdd_good_id ");

        StringBuilder sql = new StringBuilder("SELECT A.*");
        sql.append(",eg.`name` AS goodsName,eg.number AS goodsNum");
        sql.append(",pd.thumbUrl AS goodsImg,eg.id AS erpGoodsId");
        sql.append(",IFNULL(eg.cost_price,0) cost_price,IFNULL(eg.freight,0) AS freight");
        sql.append(" FROM ( ");
        sql.append(ASQL);
        sql.append(" ) AS A ");
        sql.append(" LEFT JOIN dc_shop_goods pd ON pd.goodsId = A.pdd_good_id ");
        sql.append(" LEFT JOIN erp_goods eg ON eg.id = pd.erp_goods_id");
        var lists = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper(SalesReportPddVo.class), date);
        return lists;
    }

    public List<SalesAndRefundReportPddVo> getSalesAndRefundReport(Integer shopId) {
        // String sql = "SELECT goods_id,goods_name,goods_image,SUM(quantity) AS refundTotal"+
        // ",(SELECT SUM(quantity) FROM dc_pdd_orders_item oi LEFT JOIN dc_pdd_orders o ON o.id=oi.order_id WHERE (o.order_status=2 OR o.order_status=3) AND oi.pdd_good_id=goods_id) AS salesTotal"+
        // ",eg.`name` AS goodsNick,eg.`number` AS goodsNum "+
        // " from dc_pdd_refund  "+
        // " LEFT JOIN dc_shop_goods pg ON pg.goodsId = goods_id "+
        // " LEFT JOIN erp_goods eg ON eg.id = pg.erp_goods_id "+
        //  " WHERE after_sales_type=3 AND pg.erp_goods_id IS NOT null ";
        //  if(shopId!=null && shopId>0){
        //     sql += " AND dc_pdd_refund.shopId="+shopId;
        //  }
        //  sql += " GROUP BY goods_id  ORDER BY salesTotal DESC ";

        /** 旧版
         StringBuilder sb = new StringBuilder();
         sb.append("SELECT poi.pdd_good_id as goodsId,poi.goodsName,poi.goodsImg as goodsImage ");
         sb.append(",SUM(quantity) AS salesTotal");
        //  sb.append(",IFNULL((SELECT SUM(quantity) FROM dc_pdd_refund WHERE after_sales_type=3 AND auditStatus<>99 AND goods_id=poi.pdd_good_id),0) AS refundTotal");
        sb.append(",IFNULL((SELECT SUM(quantity) AS rt FROM dc_pdd_orders_item p LEFT JOIN dc_pdd_orders o ON o.id=p.order_id WHERE p.pdd_good_id = poi.pdd_good_id AND o.refund_status<>1 AND o.order_status<>1),0) AS refundTotal");
         sb.append(",eg.`name` AS goodsNick,eg.`number` AS goodsNum");
         sb.append(" FROM dc_pdd_orders_item poi");

         sb.append(" LEFT JOIN dc_pdd_orders po ON po.id = poi.order_id ");
         sb.append(" LEFT JOIN dc_shop_goods pg ON pg.goodsId = poi.pdd_good_id");
         sb.append(" LEFT JOIN erp_goods eg ON eg.id = pg.erp_goods_id");
         sb.append(" WHERE (po.order_status=2 OR po.order_status=3)");
         sb.append(" AND pg.isOnsale = 1 ");
         if(shopId!=null && shopId>0){
            sb.append(" AND po.shopId="+shopId);
         }
        //  sb.append(" AND poi.pdd_good_id <> 394802014653 AND ")
         sb.append(" GROUP BY pdd_good_id  ");
         sb.append(" ORDER BY salesTotal DESC ");
         **/
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT poi1.pdd_good_id as goodsId,poi1.salesTotal,poi1.refundTotal,eg.`name` AS goodsNick,eg.`number` AS goodsNum,pg.thumbUrl as goodsImage  FROM ");
        sb.append("(SELECT pdd_good_id,SUM(quantity) AS salesTotal,");
        sb.append("IFNULL((SELECT SUM(quantity) AS rt FROM dc_pdd_orders_item p LEFT JOIN dc_pdd_orders o ON o.id=p.order_id WHERE p.pdd_good_id = poi.pdd_good_id AND o.refund_status<>1 AND o.order_status<>1),0) AS refundTotal");
        sb.append(" FROM dc_pdd_orders_item  poi LEFT JOIN dc_pdd_orders po ON po.id = poi.order_id  ");
        sb.append(" WHERE (po.order_status=2 OR po.order_status=3)  ");
        if(shopId!=null && shopId>0){
            sb.append(" AND po.shopId="+shopId);
        }
        sb.append(" GROUP BY pdd_good_id  ) AS poi1");
        sb.append(" LEFT JOIN dc_shop_goods pg ON pg.goodsId = poi1.pdd_good_id ");
        sb.append(" LEFT JOIN erp_goods eg ON eg.id = pg.erp_goods_id ");
        sb.append(" WHERE pg.isOnsale = 1 ORDER BY salesTotal DESC ");


        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper(SalesAndRefundReportPddVo.class));
    }

    public FinanceMonthReportPddVo getFinanceMonthReport(Integer shopId, String month) {
        try{
            StringBuilder sb= new StringBuilder();
            sb.append("SELECT ");
            sb.append("(SELECT IFNULL(SUM(income),0) FROM dc_pdd_order_settlement  WHERE orderSn IN (SELECT order_sn FROM dc_pdd_orders WHERE DATE_FORMAT(created_time,'%Y-%m') = ? ) )AS income,");
            sb.append("(SELECT IFNULL(SUM(expend),0) FROM dc_pdd_order_settlement  WHERE orderSn IN (SELECT order_sn FROM dc_pdd_orders WHERE DATE_FORMAT(created_time,'%Y-%m') = ? ))AS expend,");
            sb.append("(SELECT IFNULL(SUM(rpAmount),0) FROM erp_invoice WHERE (transType='Purchase' OR transType='OrderDaiFa') AND DATE_FORMAT(billDate,'%Y-%m') = ? ) AS goodsCost,");
            sb.append("(SELECT IFNULL(SUM(rpAmount),0) FROM erp_invoice WHERE (transType='PUR_RETURN' OR transType='DaiFaRefund') AND DATE_FORMAT(billDate,'%Y-%m') = ? ) AS goodsRefund,");
//            sb.append("(SELECT IFNULL(SUM(feeQZ + feeSS + feeCJ ),0) FROM dc_shop_traffic WHERE DATE_FORMAT( `date`,'%Y-%m') = ?  ) AS marketingFee");
            sb.append("(SELECT IFNULL(SUM(amount),0) FROM dc_pdd_marketing_fee WHERE DATE_FORMAT( `datetime`,'%Y-%m') = ? AND `type`=1 ) AS marketingFee");
            List<Object> params = new ArrayList<>();
            params.add(month);
            params.add(month);
            params.add(month);
            params.add(month);
            params.add(month);
    
           return jdbcTemplate.queryForObject(sb.toString(),new BeanPropertyRowMapper<>(FinanceMonthReportPddVo.class), params.toArray(new Object[params.size()]));
      
        }catch (Exception e) {
            return null;
        }
    }
    
}
